package com.jsy.common.util.datasource;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL建表语句解析并导出为Excel的工具类(需要修改，暂不可用)
 */
public class SqlToExcelExporter {

    // 表名匹配正则
    private static final Pattern TABLE_NAME_PATTERN = Pattern.compile("CREATE\\s+TABLE\\s+[`\"]?([^`\"\n]+)[`\"]?\\s*\\(", Pattern.CASE_INSENSITIVE);
    // 字段定义匹配正则
    private static final Pattern COLUMN_PATTERN = Pattern.compile("[`\"]?([^`\"\n]+)[`\"]?\\s+([^,\n]+)", Pattern.CASE_INSENSITIVE);
    // 注释匹配正则
    private static final Pattern COMMENT_PATTERN = Pattern.compile("COMMENT\\s+[`\"]?([^`\"\n]+)[`\"]?", Pattern.CASE_INSENSITIVE);

    /**
     * 解析SQL文件并导出为Excel
     * @param sqlFilePath SQL文件路径
     * @param excelFilePath 导出的Excel文件路径
     * @throws IOException 文件操作异常
     */
    public void exportToExcel(String sqlFilePath, String excelFilePath) throws IOException {
        // 读取SQL文件内容
        String sqlContent = readSqlFile(sqlFilePath);
        
        // 解析SQL内容，获取表信息
        List<TableInfo> tableInfos = parseSqlContent(sqlContent);
        
        // 导出到Excel
        writeToExcel(tableInfos, excelFilePath);
    }

    /**
     * 读取SQL文件内容
     */
    private String readSqlFile(String filePath) throws IOException {
        StringBuilder content = new StringBuilder();
        try (BufferedReader reader = new BufferedReader(new FileReader(filePath))) {
            String line;
            while ((line = reader.readLine()) != null) {
                content.append(line).append("\n");
            }
        }
        return content.toString();
    }

    /**
     * 解析SQL内容，提取表和字段信息
     */
    private List<TableInfo> parseSqlContent(String sqlContent) {
        List<TableInfo> tableInfos = new ArrayList<>();
        
        // 按CREATE TABLE分割SQL内容
        String[] tableDefinitions = sqlContent.split("CREATE\\s+TABLE", Pattern.CASE_INSENSITIVE);
        
        for (String tableDef : tableDefinitions) {
            tableDef = tableDef.trim();
            if (tableDef.isEmpty()) continue;
            
            // 重新添加CREATE TABLE前缀
            tableDef = "CREATE TABLE " + tableDef;
            
            // 提取表名
            Matcher tableNameMatcher = TABLE_NAME_PATTERN.matcher(tableDef);
            if (!tableNameMatcher.find()) continue;
            
            String tableName = tableNameMatcher.group(1);
            TableInfo tableInfo = new TableInfo(tableName);
            
            // 提取表注释
            Matcher commentMatcher = COMMENT_PATTERN.matcher(tableDef);
            if (commentMatcher.find()) {
                tableInfo.setComment(commentMatcher.group(1));
            }
            
            // 提取字段信息
            String[] lines = tableDef.split("\n");
            boolean isInTableBody = false;
            for (String line : lines) {
                line = line.trim();
                
                // 判断是否进入表定义主体
                if (line.startsWith("(")) {
                    isInTableBody = true;
                    continue;
                }
                
                // 判断是否离开表定义主体
                if (line.startsWith(")") || line.toUpperCase().startsWith("ENGINE")) {
                    isInTableBody = false;
                    continue;
                }
                
                // 在表定义主体内，提取字段信息
                if (isInTableBody && !line.isEmpty() && !line.startsWith(",")) {
                    ColumnInfo columnInfo = parseColumn(line);
                    if (columnInfo != null) {
                        tableInfo.addColumn(columnInfo);
                    }
                }
            }
            
            tableInfos.add(tableInfo);
        }
        
        return tableInfos;
    }

    /**
     * 解析字段定义行
     */
    private ColumnInfo parseColumn(String line) {
        // 匹配字段名和类型
        Matcher columnMatcher = COLUMN_PATTERN.matcher(line);
        if (!columnMatcher.find()) return null;
        
        String columnName = columnMatcher.group(1);
        String columnType = columnMatcher.group(2);
        
        ColumnInfo columnInfo = new ColumnInfo(columnName, columnType);
        
        // 提取字段注释
        Matcher commentMatcher = COMMENT_PATTERN.matcher(line);
        if (commentMatcher.find()) {
            columnInfo.setComment(commentMatcher.group(1));
        }
        
        // 判断是否为主键
        if (line.toUpperCase().contains("PRIMARY KEY")) {
            columnInfo.setPrimaryKey(true);
        }
        
        // 判断是否可为空
        if (line.toUpperCase().contains("NOT NULL")) {
            columnInfo.setNullable(false);
        }
        
        // 判断是否自增
        if (line.toUpperCase().contains("AUTO_INCREMENT")) {
            columnInfo.setAutoIncrement(true);
        }
        
        return columnInfo;
    }

    /**
     * 将表信息写入Excel
     */
    private void writeToExcel(List<TableInfo> tableInfos, String excelFilePath) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            // 为每个表创建一个Sheet
            for (TableInfo tableInfo : tableInfos) {
                Sheet sheet = workbook.createSheet(tableInfo.getTableName());
                
                // 创建表头
                Row headerRow = sheet.createRow(0);
                String[] headers = {"字段名", "类型", "是否主键", "是否可为空", "是否自增", "注释"};
                for (int i = 0; i < headers.length; i++) {
                    Cell cell = headerRow.createCell(i);
                    cell.setCellValue(headers[i]);
                    
                    // 设置表头样式
                    CellStyle style = workbook.createCellStyle();
                    Font font = workbook.createFont();
                    font.setBold(true);
                    style.setFont(font);
                    cell.setCellStyle(style);
                }
                
                // 填充数据
                List<ColumnInfo> columns = tableInfo.getColumns();
                for (int i = 0; i < columns.size(); i++) {
                    Row row = sheet.createRow(i + 1);
                    ColumnInfo column = columns.get(i);
                    
                    row.createCell(0).setCellValue(column.getColumnName());
                    row.createCell(1).setCellValue(column.getColumnType());
                    row.createCell(2).setCellValue(column.isPrimaryKey() ? "是" : "否");
                    row.createCell(3).setCellValue(column.isNullable() ? "是" : "否");
                    row.createCell(4).setCellValue(column.isAutoIncrement() ? "是" : "否");
                    row.createCell(5).setCellValue(column.getComment());
                }
                
                // 自动调整列宽
                for (int i = 0; i < headers.length; i++) {
                    sheet.autoSizeColumn(i);
                }
            }
            
            // 添加表清单页
            Sheet tableListSheet = workbook.createSheet("表清单");
            Row listHeaderRow = tableListSheet.createRow(0);
            listHeaderRow.createCell(0).setCellValue("表名");
            listHeaderRow.createCell(1).setCellValue("表注释");
            listHeaderRow.createCell(2).setCellValue("字段数量");
            
            for (int i = 0; i < tableInfos.size(); i++) {
                TableInfo tableInfo = tableInfos.get(i);
                Row row = tableListSheet.createRow(i + 1);
                row.createCell(0).setCellValue(tableInfo.getTableName());
                row.createCell(1).setCellValue(tableInfo.getComment());
                row.createCell(2).setCellValue(tableInfo.getColumns().size());
            }
            
            // 保存Excel文件
            try (FileOutputStream fileOut = new FileOutputStream(excelFilePath)) {
                workbook.write(fileOut);
            }
        }
    }

    /**
     * 表信息类
     */
    static class TableInfo {
        private String tableName;
        private String comment;
        private List<ColumnInfo> columns = new ArrayList<>();
        
        public TableInfo(String tableName) {
            this.tableName = tableName;
        }
        
        // getters and setters
        public String getTableName() { return tableName; }
        public String getComment() { return comment; }
        public void setComment(String comment) { this.comment = comment; }
        public List<ColumnInfo> getColumns() { return columns; }
        public void addColumn(ColumnInfo column) { this.columns.add(column); }
    }

    /**
     * 字段信息类
     */
    static class ColumnInfo {
        private String columnName;
        private String columnType;
        private String comment;
        private boolean primaryKey;
        private boolean nullable = true;
        private boolean autoIncrement;
        
        public ColumnInfo(String columnName, String columnType) {
            this.columnName = columnName;
            this.columnType = columnType;
        }
        
        // getters and setters
        public String getColumnName() { return columnName; }
        public String getColumnType() { return columnType; }
        public String getComment() { return comment; }
        public void setComment(String comment) { this.comment = comment; }
        public boolean isPrimaryKey() { return primaryKey; }
        public void setPrimaryKey(boolean primaryKey) { this.primaryKey = primaryKey; }
        public boolean isNullable() { return nullable; }
        public void setNullable(boolean nullable) { this.nullable = nullable; }
        public boolean isAutoIncrement() { return autoIncrement; }
        public void setAutoIncrement(boolean autoIncrement) { this.autoIncrement = autoIncrement; }
    }

    /**
     * 主方法，用于测试
     */
    public static void main(String[] args) {
        SqlToExcelExporter exporter = new SqlToExcelExporter();
        try {
//            exporter.exportToExcel("database.sql", "database_structure.xlsx");
            exporter.exportToExcel("F:\\jsy\\goalong\\goalong-common\\src\\main\\resources\\db_mysql_change.sql", "database_structure.xlsx");
            System.out.println("导出成功！");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}    